﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;

using EShop.Common;

namespace EShop.DAL
{
    public class MemberManagerDAL : BaseDAL
    {
        /// <summary>
        /// 获取会员信息
        /// </summary>
        /// <param name="strMemberID"></param>
        /// <param name="type"></param>
        /// <returns></returns>
        public DataTable GetMember(string strMemberID, int type)
        {
            string strUSP = "USP_MemberQuery";
            try
            {
                SqlParameter[] parameter ={
                               new SqlParameter("@MemberID",SqlDbType.VarChar,20),
                               new SqlParameter("@Type",SqlDbType.Int)
                                      };
                if (string.IsNullOrEmpty(strMemberID)) parameter[0].Value = null;
                else parameter[0].Value = strMemberID;
                parameter[1].Value = type;
                return DbHelper.ExecuteDataSetSQL(strUSP, CommandType.StoredProcedure, parameter).Tables[0];
            }
            catch (Exception ex)
            {
                log.Write((string.Format("USP: {0}, \r\n ex : {1}", strUSP, ex.Message)));
                throw;
            }
        }

        /// <summary>
        /// 获取会员等级信息
        /// </summary>
        /// <returns></returns>
        public DataTable GetLevelInfo()
        {
            string strUSP = "USP_MemberLevelInfo_Query";
            try
            {
                return DbHelper.ExecuteDataSetSQL(strUSP, CommandType.StoredProcedure).Tables[0];
            }
            catch (Exception ex)
            {
                log.Write((string.Format("USP: {0}, \r\n ex : {1}", strUSP, ex.Message)));
                throw;
            }
        }

        public int MemberInfoAppend(string strMemberID, string strMemberName,
            byte Sex, DateTime Brithday, byte IsGregorianCalendar, int LevelID,
            string MemberMobile, string MemberAddress, string Remark)
        {
            string strUSP = "USP_MemberInfo_Append";
            try
            {
                SqlParameter[] parameter ={
                                         new SqlParameter("@MemberID",SqlDbType.VarChar,20),
                                         new SqlParameter("@MemberName",SqlDbType.VarChar,50),
                                         new SqlParameter("@MemberSex",SqlDbType.Bit),
                                         new SqlParameter("@Brithday",SqlDbType.DateTime),
                                         new SqlParameter("@IsGregorianCalendar",SqlDbType.Bit),
                                         new SqlParameter("@MemberAddress",SqlDbType.VarChar,255),
                                         new SqlParameter("@MemberMobel",SqlDbType.VarChar,12),
                                         new SqlParameter("@LevelID",SqlDbType.Int),
                                         new SqlParameter("@Remark",SqlDbType.Text)
                                     };
                parameter[0].Value = strMemberID;
                parameter[1].Value = strMemberName;
                parameter[2].Value = Sex;
                parameter[3].Value = Brithday;
                parameter[4].Value = IsGregorianCalendar;
                parameter[5].Value = MemberAddress;
                parameter[6].Value = MemberMobile;
                parameter[7].Value = LevelID;
                parameter[8].Value = Remark;
                return DbHelper.ExecuteNonQuerySQL(strUSP, CommandType.StoredProcedure, parameter);
            }
            catch (Exception ex)
            {
                log.Write((string.Format("USP: {0}, \r\n ex : {1}", strUSP, ex.Message)));
                throw;
            }
        }

        public int MemberScoreAppend(string MemberID, int MemberScoreValue, byte IsLimitTime, DateTime LasLimitTime)
        {
            string strUSP = "USP_MemberScoreAppend";
            try
            {
                SqlParameter[] parameter ={
                                         new SqlParameter("@MemberID",SqlDbType.VarChar,20),
                                         new SqlParameter("@MemberScoreValue",SqlDbType.Int),
                                         new SqlParameter("@IsLimitTime",SqlDbType.Bit),
                                         new SqlParameter("@LasLimitTime",SqlDbType.DateTime)
                                     };
                parameter[0].Value = MemberID;
                parameter[1].Value = MemberScoreValue;
                parameter[2].Value = IsLimitTime;
                parameter[3].Value = LasLimitTime;
                return DbHelper.ExecuteNonQuerySQL(strUSP, CommandType.StoredProcedure, parameter);
            }
            catch (Exception ex)
            {
                log.Write((string.Format("USP: {0}, \r\n ex : {1}", strUSP, ex.Message)));
                throw;
            }
        }

        public int MemberInfoUpdate(string strMemberID, string strMemberName,
            byte Sex, DateTime Brithday, byte IsGregorianCalendar, int LevelID,
            string MemberMobile, string MemberAddress, string Remark)
        {
            string strUSP = "USP_MemberInfo_Update";
            try
            {
                SqlParameter[] parameter ={
                                         new SqlParameter("@MemberID",SqlDbType.VarChar,20),
                                         new SqlParameter("@MemberName",SqlDbType.VarChar,50),
                                         new SqlParameter("@MemberSex",SqlDbType.Bit),
                                         new SqlParameter("@Brithday",SqlDbType.DateTime),
                                         new SqlParameter("@IsGregorianCalendar",SqlDbType.Bit),
                                         new SqlParameter("@MemberAddress",SqlDbType.VarChar,255),
                                         new SqlParameter("@MemberMobel",SqlDbType.VarChar,12),
                                         new SqlParameter("@LevelID",SqlDbType.Int),
                                         new SqlParameter("@Remark",SqlDbType.Text)
                                     };
                parameter[0].Value = strMemberID;
                parameter[1].Value = strMemberName;
                parameter[2].Value = Sex;
                parameter[3].Value = Brithday;
                parameter[4].Value = IsGregorianCalendar;
                parameter[5].Value = MemberAddress;
                parameter[6].Value = MemberMobile;
                parameter[7].Value = LevelID;
                parameter[8].Value = Remark;
                return DbHelper.ExecuteNonQuerySQL(strUSP, CommandType.StoredProcedure, parameter);
            }
            catch (Exception ex)
            {
                log.Write((string.Format("USP: {0}, \r\n ex : {1}", strUSP, ex.Message)));
                throw;
            }
        }

        public int MemberLevelInfoAppend(string LevelName, decimal LevelDiscount)
        {
            string strUSP = "USP_MemberLevelInfo_Append";
            try
            {
                SqlParameter[] parameter ={
                                         new SqlParameter("@LevelName",SqlDbType.VarChar,50),
                                         new SqlParameter("@LevelDiscount",SqlDbType.Decimal)
                                     };
                parameter[0].Value = LevelName;
                parameter[1].Value = LevelDiscount;
                return DbHelper.ExecuteNonQuerySQL(strUSP, CommandType.StoredProcedure, parameter);
            }
            catch (Exception ex)
            {
                log.Write((string.Format("USP: {0}, \r\n ex : {1}", strUSP, ex.Message)));
                throw;
            }
        }

        public int MemberLevelInfoUpdate(string LevelID, string LevelName, decimal LevelDiscount)
        {
            string strUSP = "USP_MemberLevelInfo_Update";
            try
            {
                SqlParameter[] parameter ={
                                         new SqlParameter("@LevelID",SqlDbType.Int),
                                         new SqlParameter("@LevelName",SqlDbType.VarChar,50),
                                         new SqlParameter("@LevelDiscount",SqlDbType.Decimal)
                                     };
                parameter[0].Value = LevelID;
                parameter[1].Value = LevelName;
                parameter[2].Value = LevelDiscount;
                return DbHelper.ExecuteNonQuerySQL(strUSP, CommandType.StoredProcedure, parameter);
            }
            catch (Exception ex)
            {
                log.Write((string.Format("USP: {0}, \r\n ex : {1}", strUSP, ex.Message)));
                throw;
            }
        }


        public DataTable GetMemberConsumeHistoryYear(string strMemberID)
        {
            string strUSP = "USP_MemberConsumeHistory_Year_Query";
            try
            {
                SqlParameter[] parameter ={
                                         new SqlParameter("@MemberID",SqlDbType.VarChar,20)
                                     };
                parameter[0].Value = strMemberID;
                return DbHelper.ExecuteDataSetSQL(strUSP, CommandType.StoredProcedure, parameter).Tables[0];
            }
            catch (Exception ex)
            {
                log.Write((string.Format("USP: {0}, \r\n ex : {1}", strUSP, ex.Message)));
                throw;
            }
        }

        public DataTable GetMemberConsumeHistoryMonth(string strMemberID, string Year)
        {
            string strUSP = "USP_MemberConsumeHistory_Month_Query";
            try
            {
                SqlParameter[] parameter ={
                                         new SqlParameter("@MemberID",SqlDbType.VarChar,20),
                                         new SqlParameter("@Year",SqlDbType.VarChar,4)
                                     };
                parameter[0].Value = strMemberID;
                parameter[1].Value = Year;
                return DbHelper.ExecuteDataSetSQL(strUSP, CommandType.StoredProcedure, parameter).Tables[0];
            }
            catch (Exception ex)
            {
                log.Write((string.Format("USP: {0}, \r\n ex : {1}", strUSP, ex.Message)));
                throw;
            }
        }

        public DataTable GetMemberConsumeHistoryDay(string strMemberID, string Year,string Month)
        {
            string strUSP = "USP_MemberConsumeHistory_Day_Query";
            try
            {
            SqlParameter[] parameter ={
                                         new SqlParameter("@MemberID",SqlDbType.VarChar,20),
                                         new SqlParameter("@Year",SqlDbType.VarChar,4),
                                         new SqlParameter("@Month",SqlDbType.VarChar,2)
                                     };
            parameter[0].Value = strMemberID;
            parameter[1].Value = Year;
            parameter[2].Value = Month;
            return DbHelper.ExecuteDataSetSQL(strUSP, CommandType.StoredProcedure, parameter).Tables[0];
            }
            catch (Exception ex)
            {
                log.Write((string.Format("USP: {0}, \r\n ex : {1}", strUSP, ex.Message)));
                throw;
            }
        }

        public DataSet GetMemberConsumeHistory(string strMemberID, int iType, string strYear, string strMonth, string strDay)
        {
            string strUSP = "USP_MemberConsumeHistory";
            try
            {
            SqlParameter[] parameter ={
                                         new SqlParameter("@MemberID",SqlDbType.VarChar,20),
                                         new SqlParameter("@Type",SqlDbType.Int),
                                         new SqlParameter("@Year",SqlDbType.VarChar,4),
                                         new SqlParameter("@Month",SqlDbType.VarChar,2),
                                         new SqlParameter("@Day",SqlDbType.VarChar,2)
                                     };
            parameter[0].Value = strMemberID;
            parameter[1].Value = iType;
            parameter[2].Value = strYear;
            parameter[3].Value = strMonth;
            parameter[4].Value = strDay;
            return DbHelper.ExecuteDataSetSQL(strUSP, CommandType.StoredProcedure, parameter);
            }
            catch (Exception ex)
            {
                log.Write((string.Format("USP: {0}, \r\n ex : {1}", strUSP, ex.Message)));
                throw;
            }
        }

    }
}
